package modelo.bd.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import modelo.bd.ConexionPool;
import modelo.bd.excepcion.DAOException;
import modelo.dominio.Proveedor;

public class ProveedorDAO {

    private String query;

    protected List<Proveedor> select() throws DAOException {
        Connection conexion = null;
        PreparedStatement sentencia = null;
        ResultSet resultado = null;
        List<Proveedor> resultados = null;
        try {
            conexion = ConexionPool.getInstancia().getConexion();
            sentencia = conexion.prepareStatement(query);
            resultado = sentencia.executeQuery();
            resultados = new ArrayList<Proveedor>();
            while (resultado.next()) {
                Proveedor proveedor = new Proveedor();
                proveedor.setIdentificador(resultado.getLong("identificador"));
                proveedor.setRazonSocial(resultado.getString("razon_social"));
                proveedor.setRfc(resultado.getString("rfc"));
                resultados.add(proveedor);
            }
        } catch (SQLException ex) {
            throw new DAOException("Error PRDS-SQL " + ex.getMessage());
        } finally {
            try {
                if (resultado != null) {
                    resultado.close();
                }
                if (sentencia != null) {
                    sentencia.close();
                }
                if (conexion != null) {
                    conexion.close();
                }
            } catch (SQLException ex) {
                throw new DAOException("Error PRDS-SQL " + ex.getMessage());
            }
        }
        return resultados;
    }

    public List<Proveedor> selectAll() throws DAOException {
        query = "SELECT DISTINCT prpo_numero AS identificador, \n"
                + "       prpo_razon_social    AS razon_social, \n"
                + "       prpo_rfc             AS rfc \n"
                + "FROM   sil_v_proveedores_po, \n"
                + "       apps.ap_invoices_all@dbl_sil_sil_ebs ai, apps.po_vendors@dbl_sil_sil_ebs pv \n"
                + "WHERE  org_id                        = 370 \n"
                + "AND    pv.vendor_id                  = ai.vendor_id \n"
                + "AND    NVL(payment_status_flag, 'N') = 'N' \n"
                + "AND    cancelled_date                IS NULL \n"
                + "AND    project_id                    IS NULL \n"
                + "AND    pv.segment1                   = prpo_numero \n"
                + "AND    apps.ap_invoices_pkg.get_posting_status@dbl_sil_sil_ebs (invoice_id) = 'Y' \n"
                + "AND    SIL_F_MUESTRA_FACTURA( pv.segment1, ai.invoice_num) = 'S' \n"
                + "ORDER BY 2";
//        query = "SELECT prpo_numero       AS identificador, \n"
//                    + "       prpo_razon_social AS razon_social, \n"
//                    + "       prpo_rfc          AS rfc \n"
//                    + "FROM   sil_v_proveedores_po \n"
//                    + "WHERE  EXISTS (SELECT 'X' \n"
//                    + "               FROM   apps.ap_invoices_all@dbl_sil_sil_ebs ai, "
//                    + "                      apps.po_vendors@dbl_sil_sil_ebs pv \n"
//                    + "               WHERE  org_id                        = 370 \n"
//                    + "               AND    pv.vendor_id                  = ai.vendor_id \n"
//                    + "               AND    NVL(payment_status_flag, 'N') = 'N' \n"
//                    + "               AND    cancelled_date                IS NULL \n"
//                    + "               AND    project_id                    IS NULL \n"
//                    + "               AND    pv.segment1                   = prpo_numero \n"
//                    + "               AND    apps.ap_invoices_pkg.get_posting_status@dbl_sil_sil_ebs (invoice_id) = 'Y' \n"
//                    + "               ) \n"
//                    + " ORDER BY to_number(prpo_numero)";
        return select();
    }

    public List<Proveedor> selectWhere(String condition) throws DAOException {
        query = "SELECT prpo_numero       AS identificador, \n"
                + "       prpo_razon_social AS razon_social, \n"
                + "       prpo_rfc          AS rfc \n"
                + "FROM   sil_v_proveedores_po \n"
                + "WHERE " + condition
                + "AND    EXISTS (SELECT 'X' \n"
                + "               FROM   apps.ap_invoices_all@dbl_sil_sil_ebs ai, apps.po_vendors@dbl_sil_sil_ebs pv \n"
                + "               WHERE  org_id                        = 370 \n"
                + "               AND    pv.vendor_id                  = ai.vendor_id \n"
                + "               AND    NVL(payment_status_flag, 'N') = 'N' \n"
                + "               AND    cancelled_date                IS NULL \n"
                + "               AND    project_id                    IS NULL \n"
                + "               AND    pv.segment1                   = prpo_numero \n"
                + "               AND    apps.ap_invoices_pkg.get_posting_status@dbl_sil_sil_ebs (invoice_id) = 'Y' \n"
                + "               ) \n"
                + "ORDER BY to_number(prpo_numero)";
        return select();
    }
}
